<?php

class Admin_Input_Model extends Model
{
	// find a way to use variable to reuse the code by recalling the same method
	var $images_dir;
	var $images_dir_url;
	
	public function __construct()
	{
		parent::Model();
		
		// open the database
		$this->load->database();
	}
	
	public function get_province_list()
	{
		$is_delete=0;

		$result = $this->db->query('SELECT state_province_name
							FROM state_province
							WHERE is_deleted = \''.$is_delete.'\'
							ORDER BY state_province_name');

		$province_state_list=array();
		$province_state_list[0]="";
		
		$i=1;
		foreach($result->result() as $row)
		{
			$province_state_list[$i] = $row->state_province_name;
			$i++;
		}
		
		return $province_state_list;
	}
	
	function get_result($query)
	{
		$result = $this->db->query($query);
		return $result->result();
	}
	
	function get_result_for_pagination($query, $per_page, $segment)
	{
		$result = $this->db->query($query, $per_page, $segment);		
		return $result->result();
	}

	function list_quotation($start, $finish)//, $per_page, $segment)
	{
//		$segment=10;
//		echo $start." | ".$finish." | ".$per_page." | ".$segment;
		
		$result="";
		
		if(strcmp($start,"")!=0 && strcmp($finish,"")!=0)
		{
			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation 
								WHERE date BETWEEN \''.$start.'\' AND \''.$finish.'\'');
//								LIMIT '.$segment.', '.$per_page.'');
		}
/*			
		else if(strcmp($start,"")!=0)
		{
			$finish="9999-01-01";

			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation 
								WHERE date BETWEEN \''.$start.'\' AND \''.$finish.'\'
								LIMIT '.$segment.' '.$per_page.'');
			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation 
								WHERE date BETWEEN \''.$start.'\' AND \''.$finish.'\'');
		}
		else if(strcmp($finish,"")!=0)
		{
			$start="0000-00-00";

			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation 
								WHERE date BETWEEN \''.$start.'\' AND \''.$finish.'\'
								LIMIT '.$segment.' '.$per_page.'');
			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation 
								WHERE date BETWEEN \''.$start.'\' AND \''.$finish.'\'');
		}
		else 
		{
			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation 
								LIMIT '.$segment.' '.$per_page.'');
			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation ');
		}
*/
		
		return $result->result();

	}

	function list_quotation_pickup($start, $finish)
	{
//		echo $start."  ";
//		echo $finish;
		
		$result="";
		
		if(strcmp($start,"")!=0 && strcmp($finish,"")!=0)
		{
			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation 
								WHERE pickup_date BETWEEN \''.$start.'\' AND \''.$finish.'\'');
		}
		else if(strcmp($start,"")!=0)
		{
			$finish="9999-01-01";
			
			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation 
								WHERE pickup_date BETWEEN \''.$start.'\' AND \''.$finish.'\'');
		}
		else if(strcmp($finish,"")!=0)
		{
			$start="0000-00-00";
			
			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation 
								WHERE pickup_date BETWEEN \''.$start.'\' AND \''.$finish.'\'');
		}
		else 
		{
			$result = $this->db->query('SELECT quote_id, date, pickup_date, requester_name, phone, total_weight, pieces
								FROM quotation ');
		}
		
		return $result->result();

	}
	
	public function list_search_city($city)
	{
		$is_delete=0;

		$search_city=array();

		if(strcmp($city,"")==0)
		{
			$result = $this->db->query('SELECT city_id, city_name, state_province_id
								FROM city
								WHERE is_deleted = \''.$is_delete.'\'');
		}
		else
		{
			$result = $this->db->query('SELECT city_id, city_name, state_province_id
								FROM city 
								WHERE city_name like \'%'.$city.'%\' AND is_deleted = \''.$is_delete.'\'');
		}

		$i=0;
		
		if($result->num_rows() > 0)
		foreach($result->result() as $row)
		{
			$search_city[$i]['id']=$row->city_id;
			$search_city[$i]['city']=$row->city_name;
			$search_city[$i]['prov']="";
			$search_city[$i]['country']="";
			
			$sql = $this->db->query('SELECT state_province_name, country_name
										FROM state_province 
										WHERE state_province_id = \''.$row->state_province_id .'\'');
							
//			$sql = $this->db->where('state_province_id =', $row->state_province_id);

			if($sql->num_rows() > 0)
				foreach($sql->result() as $line)
				{
					$search_city[$i]['prov']=$line->state_province_name;
					$search_city[$i]['country']=$line->country_name;
				}
//			else
//				echo "does not exist";
			
			$i++;
		}
		
		return $search_city;
	}
	
	public function province_state_list($prov)
	{
		$is_delete=0;

		if(strcmp($prov,"")==0)
		{
			$result = $this->db->query('SELECT state_province_name, state_province_id, country_name
							FROM state_province 
							WHERE is_deleted = \''.$is_delete.'\'');
		}
		else
		{
			$result = $this->db->query('SELECT state_province_name, state_province_id, country_name
							FROM state_province
							WHERE state_province_name like \'%'.$prov.'%\' AND is_deleted = \''.$is_delete.'\'');
		}
		
		$i=0;
		$province_state_list=array();
		
		foreach($result->result() as $row)
		{
			$province_state_list[$i]['id'] = $row->state_province_id;
			$province_state_list[$i]['province'] = $row->state_province_name;
			$province_state_list[$i]['country'] = $row->country_name;

			$i++;
		}

		return $province_state_list;
	}

	public function search_city($city, $province)
	{
		$is_delete=0;

		// finding the id
		$result = $this->db->query('SELECT city_id, state_province_id
							FROM city 
							WHERE city_name = \''.$city.'\' AND is_deleted = \''.$is_delete.'\'');
							
		foreach ($result->result() as $row1)
		{
			$result = $this->db->query('SELECT state_province_name, state_province_id
										FROM state_province 
										WHERE state_province_id = \''.$row1->state_province_id.'\'');
			
			if($result->num_rows())
				foreach ($result->result() as $row2)
				{
					if(strcmp($province, $row2->state_province_name)==0)
					{
						return 1;
					}
				}
		}

		return 0;
	}
	
	function search_province_state($province, $country)
	{
		$is_delete=0;
		
		$result = $this->db->query('SELECT state_province_name, state_province_id, country_name
									FROM state_province 
									WHERE state_province_name = \''.$province.'\' AND is_deleted = \''.$is_delete.'\' AND country_name = \''.$country.'\'');

		if ($result->num_rows() > 0)
		{
			foreach ($result->result() as $row)
			{
				return $row->state_province_id;
			}
		}
		else
		{
			return 0;
		}
	}

	function add_city($city, $province)
	{
		$id=$this->search_province_state($province, "USA");
		if($id==0)
		{
			$id=$this->search_province_state($province, "CANADA");
			
			if($id>0)
			{
				$this->db->query("INSERT INTO city (city_name, state_province_id)
									VALUES (".$this->db->escape($city).", ".$this->db->escape($id).")");

				return 1;
			}
		}
		else
		{
			$this->db->query("INSERT INTO city (city_name, state_province_id)
								VALUES (".$this->db->escape($city).", ".$this->db->escape($id).")");

			return 1;
		}
		
		return 0;
	}

	function add_province($province, $country)
	{
		$this->db->query("INSERT INTO state_province (state_province_name, country_name)
							VALUES (".$this->db->escape($province).", ".$this->db->escape($country).")");

		return 1;
	}

	function delete_city($id)
	{
		$delete=1;
	/*
		$this->db->query('DELETE FROM city 
						WHERE city_id = \''.$id.'\'');
	*/
		$this->db->query('UPDATE city 
							SET is_deleted =\''. $delete.'\'
							WHERE city_id  =\''.$id.'\'');
	}
	
	function delete_province($id)
	{
		$delete=1;
	/*
		$this->db->query('DELETE FROM state_province 
						WHERE state_province_id = \''.$id.'\'');
	*/
	
		//delete all the cities in the province
		
		//1. search all the cities under that province
		//2. put them in an array
		$result=$this->search_city_under_province($id);		
		
		foreach ($result->result() as $row)
		{
			//3. call delete city
			$this->delete_city($row->city_id);
		}
		
		//delete province
		$this->db->query('UPDATE state_province
							SET is_deleted =\''. $delete.'\'
							WHERE state_province_id = \''.$id.'\'');

	}
	
	function search_city_under_province($id)
	{
		$is_delete=0;
	
		$result = $this->db->query('SELECT city_id
									FROM city
									WHERE state_province_id = \''.$id.'\' AND is_deleted = \''.$is_delete.'\'');

		return $result;
	}
}

?>